<?
include '../constants.php';
function sqlite_table_exists(&$sqlite, $table)
{
    $result = $sqlite->query("SELECT name FROM sqlite_master WHERE type='table' AND name='$table'");
    return $result->numRows() > 0;
}

$db=new SQLiteDatabase("../db/mandi");

if(sqlite_table_exists($db, "employees")){
	$db->query("drop table employees");
}
$db->query("CREATE TABLE employees (
	id				INTEGER PRIMARY KEY NOT NULL,
	name			VARCHAR(255),
	addr_street		VARCHAR(255),
	addr_state		VARCHAR(255),
	addr_pincode	VARCHAR(255),
	public_key		VARCHAR(255) UNIQUE,
	status			VARCHAR(255),
	created_at		NUMERIC
);");

if(sqlite_table_exists($db, "licenses")){
	$db->query("drop table licenses");
}
$db->query("CREATE TABLE licenses (
	name			VARCHAR(255),
	addr_street		VARCHAR(255),
	addr_state		VARCHAR(255),
	addr_pincode		VARCHAR(255),
	license_seller		INTEGER UNIQUE,
	license_buyer		INTEGER UNIQUE,
	organization_name	VARCHAR(255),
	created_at		NUMERIC
);");

if(sqlite_table_exists($db, "product_table")){
	$db->query("drop table product_table");
}
$db->query("CREATE TABLE product_table (
	id		INTEGER PRIMARY KEY,
	name_en		VARCHAR(255),
	name_hindi	VARCHAR(255),
	created_at		NUMERIC
);");

if(sqlite_table_exists($db, "entry_table")){
	$db->query("drop table entry_table");
}

$db->query("CREATE TABLE entry_table (
	employee_number    	REFERENCES employees(id),
	id					VARCHAR(255) PRIMARY KEY,
	timestamp			NUMERIC,
	driver_name			VARCHAR(255),
	vehicle_number		INTEGER,
	vehicle_type		VARCHAR(255),
	product_id			REFERENCES product_table(id),
	product_weight		REAL,
	product_farm		VARCHAR(255),
	bearer_license		INTEGER, 
	comm_agent_license	INTEGER,
	fees				REAL
);");

if(sqlite_table_exists($db, "sale_table")){
	$db->query("drop table sale_table");
}
$db->query("CREATE TABLE sale_table (
	market_name		VARCHAR(255),
	employee_number		REFERENCES employees(id),
	sold_timestamp		NUMERIC,
	departed_timestamp	NUMERIC,
	entry_id		VARCHAR(255),
	toll_number		INTEGER,
	seller_license		REFERENCES licenses(license_seller),
	buyer_license		REFERENCES licenses(license_buyer),
	produce_id		REFERENCES product_table(id),
	weight			REAL,
	rate_per_quintal	REAL,
	total_sale		REAL,
	fees_unload		REAL,
	fees_clean		REAL,
	fees_weigh		REAL,
	fees_load		REAL,
	fees_stitch		REAL,
	fees_pack		REAL,
	commissions_comm_agent	REAL,
	commissions_mandi	REAL
);");


$db->query("INSERT INTO product_table (name_en, name_hindi, created_at) VALUES (\"wheat indian\", \"\",strftime('%s','now'));
INSERT INTO product_table (name_en, name_hindi, created_at) VALUES (\"wheat mexican\", \"\",strftime('%s','now'));
INSERT INTO product_table (name_en, name_hindi, created_at) VALUES (\"peas\", \"\",strftime('%s','now'));
INSERT INTO product_table (name_en, name_hindi, created_at) VALUES (\"sunflower seed\", \"\",strftime('%s','now'));
INSERT INTO product_table (name_en, name_hindi, created_at) VALUES (\"bengal gram\", \"\",strftime('%s','now'));
INSERT INTO product_table (name_en, name_hindi, created_at) VALUES (\"dark gram\", \"\",strftime('%s','now'));
INSERT INTO product_table (name_en, name_hindi, created_at) VALUES (\"chilli\", \"\",strftime('%s','now'));
INSERT INTO product_table (name_en, name_hindi, created_at) VALUES (\"rice\", \"\",strftime('%s','now'));
INSERT INTO product_table (name_en, name_hindi, created_at) VALUES (\"jaggery\", \"\",strftime('%s','now'));
INSERT INTO product_table (name_en, name_hindi, created_at) VALUES (\"sugar\", \"\",strftime('%s','now'));
INSERT INTO product_table (name_en, name_hindi, created_at) VALUES (\"mustard seed\", \"\",strftime('%s','now'));
INSERT INTO product_table (name_en, name_hindi, created_at) VALUES (\"corn\", \"\",strftime('%s','now'));
INSERT INTO product_table (name_en, name_hindi, created_at) VALUES (\"paddy\", \"\",strftime('%s','now'));
INSERT INTO product_table (name_en, name_hindi, created_at) VALUES (\"cotton seed\", \"\",strftime('%s','now'));
");


$db->query("INSERT INTO licenses VALUES (\"Rosemary Ehlers\", \"2326 California\", \"California\", 94040, 33, NULL, \"Funny Farm\",strftime('%s','now'));
INSERT INTO licenses VALUES (\"Shrestha Chowdhury\", \"123 Escondido\", \"California\", 94305, 34, 26, \"CAPSLOCK\",strftime('%s','now'));
INSERT INTO licenses VALUES (\"Chandan Chowdhury\", \"666 Place\", \"New Delhi\", 666666, NULL, 66, \"Invigorare\",strftime('%s','now'));
INSERT INTO licenses vALUES (\"Wilson Restrepo\", \"Columbae\", \"California\", 94305, 35, NULL, \"Stanford\",strftime('%s','now'));");


$db->query('INSERT INTO entry_table VALUES (1, "1.1305053862", "1305053862", "Rosemary Ehlers", 12345, "Car", 3, 12.5, "Funny Farm", 33, 26, 6.53);
INSERT INTO entry_table VALUES (1, "1.1305053922", "1305053922", "Jeff Chern", 12358, "Giraffe", 2, 2.5, "Stanford", 35, 26, 5.53);');

$db->query(sqlite_escape_string('INSERT INTO sale_table VALUES("Market Name", 1, "1305055662", NULL, "1.1305053862", 345, 33, 26, 3, 6, 1.5, 9, 0.2, 0.2, 0.2, 0.3, 0.3, 0.3, 0.01, 0.01);
INSERT INTO sale_table VALUES("Market Name", 1, "1305055722", NULL, "1.1305053862", 345, 33, 66, 3, 6.5, 1.5, 9.75, 0.2, 0.2, 0.2, 0.3, 0.3, 0.3, 0.01, 0.01);
INSERT INTO sale_table VALUES("Narela Mandi", 1, '.(time()-(23*60*60)).', NULL, "1.1305053862", 348, 33, 26, 1, 70, 30, 2100, '.(FEES_UNLOAD*70).', '.(FEES_CLEAN*70).', '.(FEES_WEIGH*70).', '.(FEES_LOAD*70).', '.(FEES_STITCH*70).', '.(FEES_PACK*70).', '.(COMMISSION_COMM_AGENT*2100).', '.(COMMISSION_MANDI*2100).');
INSERT INTO sale_table VALUES("Narela Mandi", 2, '.(time()-(12*60*60)).', NULL, "1.1305053862", 352, 33, 66, 1, 3000, 50, 150000, '.(FEES_UNLOAD*3000).', '.(FEES_CLEAN*3000).', '.(FEES_WEIGH*3000).', '.(FEES_LOAD*3000).', '.(FEES_STITCH*3000).', '.(FEES_PACK*3000).', '.(COMMISSION_COMM_AGENT*150000).', '.(COMMISSION_MANDI*150000).');
INSERT INTO sale_table VALUES("Narela Mandi", 1, '.(time()-(20*60*60)).', NULL, "1.1305053862", 350, 33, 66, 2, 70000, 40, 2800000, '.(FEES_UNLOAD*70000).', '.(FEES_CLEAN*70000).', '.(FEES_WEIGH*70000).', '.(FEES_LOAD*70000).', '.(FEES_STITCH*70000).', '.(FEES_PACK*70000).', '.(COMMISSION_COMM_AGENT*2800000).', '.(COMMISSION_MANDI*2800000).');
INSERT INTO sale_table VALUES("Narela Mandi", 2, '.(time()-(19*60*60)).', NULL, "1.1305053862", 351, 33, 26, 3, 1000, 70, 70000, '.(FEES_UNLOAD*1000).', '.(FEES_CLEAN*1000).', '.(FEES_WEIGH*1000).', '.(FEES_LOAD*1000).', '.(FEES_STITCH*1000).', '.(FEES_PACK*1000).', '.(COMMISSION_COMM_AGENT*70000).', '.(COMMISSION_MANDI*70000).');
INSERT INTO sale_table VALUES("Narela Mandi", 2, '.(time()-(17*60*60)).', NULL, "1.1305053862", 351, 33, 26, 3, 10, 60, 600, '.(FEES_UNLOAD*10).', '.(FEES_CLEAN*10).', '.(FEES_WEIGH*10).', '.(FEES_LOAD*10).', '.(FEES_STITCH*10).', '.(FEES_PACK*10).', '.(COMMISSION_COMM_AGENT*600).', '.(COMMISSION_MANDI*600).');
INSERT INTO sale_table VALUES("Narela Mandi", 2, '.(time()-(5*60*60)).', NULL, "1.1305053862", 351, 33, 26, 3, 10, 61, 610, '.(FEES_UNLOAD*10).', '.(FEES_CLEAN*10).', '.(FEES_WEIGH*10).', '.(FEES_LOAD*10).', '.(FEES_STITCH*10).', '.(FEES_PACK*10).', '.(COMMISSION_COMM_AGENT*610).', '.(COMMISSION_MANDI*610).');
INSERT INTO sale_table VALUES("Narela Mandi", 2, '.(time()-(4*60*60)).', NULL, "1.1305053862", 351, 33, 26, 3, 10, 58, 580, '.(FEES_UNLOAD*10).', '.(FEES_CLEAN*10).', '.(FEES_WEIGH*10).', '.(FEES_LOAD*10).', '.(FEES_STITCH*10).', '.(FEES_PACK*10).', '.(COMMISSION_COMM_AGENT*580).', '.(COMMISSION_MANDI*580).');
INSERT INTO sale_table VALUES("Narela Mandi", 1, '.(time()-(60*60)).', NULL, "1.1305053862", 353, 33, 26, 3, 2000, 40, 80000, '.(FEES_UNLOAD*2000).', '.(FEES_CLEAN*2000).', '.(FEES_WEIGH*2000).', '.(FEES_LOAD*2000).', '.(FEES_STITCH*2000).', '.(FEES_PACK*2000).', '.(COMMISSION_COMM_AGENT*80000).', '.(COMMISSION_MANDI*80000).');'));

$db->query('INSERT INTO employees VALUES(1, "Kalinda Sharma", "A-240 Vasant Kunj", "New Delhi", "110019", "abc123", "ACTIVE", strftime("%s","now"));
			INSERT INTO employees VALUES(2, "Eli Gold", "666 Politicians Way", "New York", "90435", "whut", "ACTIVE", strftime("%s","now"));');

echo 'PRODUCT_TABLE<BR>';
$result=$db->query("select * from product_table");
while($row=$result->fetch(SQLITE_ASSOC)){
	var_dump($row);
	echo '<br />';
}

echo '<br><br>LICENSES<BR>';
$result=$db->query("select * from licenses");
while($row=$result->fetch(SQLITE_ASSOC)){
	var_dump($row);
	echo '<br />';
}

echo '<br><br>EMPLOYEES<BR>';
$result=$db->query("select * from employees");
while($row=$result->fetch(SQLITE_ASSOC)){
	var_dump($row);
	echo '<br />';
}

echo '<BR><BR>ENTRY_TABLE<BR>';
$result=$db->query("select * from entry_table");
while($row=$result->fetch(SQLITE_ASSOC)){
	var_dump($row);
	echo '<br />';
}

echo '<BR><BR>SALE_TABLE<BR>';
$result=$db->query("select * from sale_table");
while($row=$result->fetch(SQLITE_ASSOC)){
	var_dump($row);
	echo '<br />';
}


?>